﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using BEELab.Data;
using System.Data;
using System.Data.SqlClient;

namespace BEELab.Logic.ProgramManagement.SessionManagement
{
    public static class SessionManagement
    {
        /// <summary>
        /// Creates new session and insert it to DB
        /// </summary>
        /// <param name="ProgramID">Program ID</param>
        /// <param name="Name">Session name</param>
        /// <param name="Description">Session Description</param>
        /// <returns>True - if secceeded, False - otherwise</returns>
        public static Boolean CreateSession(int ProgramID, string Name, string Description)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "INSERT INTO Sessions (ProgramID, Name, Description) VALUES (@ProgramID, @Name, @Description)";
            command.Parameters.Add(new SqlParameter("@ProgramID", ProgramID));
            command.Parameters.Add(new SqlParameter("@Name", Name));
            command.Parameters.Add(new SqlParameter("@Description", Description));

            int res = DBHelper.ExecuteNonQuery(command);

            if (res == 1)
                return true;

            return false;
        }

        /// <summary>
        /// Gets Session Id and delete session from DB
        /// </summary>
        /// <param name="ID">Session ID</param>
        /// <returns>True if secceeded, False otherwise</returns>
        public static Boolean DeleteSession(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "DELETE FROM InitialValues WHERE Type='Invitation' AND ParentID IN (SELECT ID FROM Invitations WHERE SessionID=@ID); DELETE FROM InitialValues WHERE Type='Session' AND ParentID=@ID; DELETE FROM Invitations WHERE SessionID=@ID; DELETE FROM Subjects WHERE SessionID=@ID; DELETE FROM Sessions WHERE ID=@ID;";
            command.Parameters.Add(new SqlParameter("@ID", ID));
            return DBHelper.ExecuteNonQuery(command) != 0;
        }

        /// <summary>
        /// Gets Session Id and updates session name and description on DB
        /// </summary>
        /// <param name="ID">Session ID</param>
        /// <param name="Name">Session new name</param>
        /// <param name="Description">Session new description</param>
        /// <returns>True if secceeded, False otherwise</returns>
        public static Boolean UpdateSession(int ID, string Name, string Description)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "UPDATE Sessions SET Name=@Name, Description=@Description WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@Name", Name));
            command.Parameters.Add(new SqlParameter("@Description", Description));
            command.Parameters.Add(new SqlParameter("@ID", ID));

            int res = DBHelper.ExecuteNonQuery(command);

            if (res == 1)
                return true;

            return false;
        }

        /// <summary>
        /// Updates one variable value in an Experiment's Session. 
        /// If variabe doesn't exists on this Session, will add it to Session.
        /// </summary>
        /// <param name="SessionID">Session ID</param>
        /// <param name="VariableID">Variable ID</param>
        /// <param name="Value">New Variable value</param>
        /// <returns>True if succeeded, False otherwise</returns>
        public static Boolean SetVariableValue(int SessionID, int VariableID, string Value)
        {
            SqlCommand command = new SqlCommand();
            int res;

            //If VariableID is 0, update FirstStage on Session
            if (VariableID == 0)
            {
                command = new SqlCommand();
                command.CommandText = "UPDATE Sessions SET FirstStage=@Value WHERE ID=@SessionID";
                command.Parameters.Add(new SqlParameter("@Value", Value));
                command.Parameters.Add(new SqlParameter("@SessionID", SessionID));
                res = DBHelper.ExecuteNonQuery(command);

                if (res > 0)
                    return true;

                return false;
            }

            //else, if it's not firstStage- update other existing variable according to its variableID
            command = new SqlCommand();
            command.CommandText = "UPDATE InitialValues SET Value=@Value WHERE ParentID=@ParentID AND VariableID=@VariableID AND Type='Session'";
            command.Parameters.Add(new SqlParameter("@Value", Value));
            command.Parameters.Add(new SqlParameter("@ParentID", SessionID));
            command.Parameters.Add(new SqlParameter("@VariableID", VariableID));

            res = DBHelper.ExecuteNonQuery(command);

            if (res > 0)
                return true;

            //else, if it's not an existing variable, add it as new variable
            command = new SqlCommand();
            command.CommandText = "INSERT INTO InitialValues (ParentID, VariableID, Value, Type) VALUES (@ParentID, @VariableID, @Value, @Type)";
            command.Parameters.Add(new SqlParameter("@Value", Value));
            command.Parameters.Add(new SqlParameter("@ParentID", SessionID));
            command.Parameters.Add(new SqlParameter("@VariableID", VariableID));
            command.Parameters.Add(new SqlParameter("@Type", "Session"));

            res = DBHelper.ExecuteNonQuery(command);

            if (res == 1)
                return true;

            return false;
        }

        /// <summary>
        /// Gets SessionID and variable Name,
        /// and returns the value of the variable in the specific Session
        /// </summary>
        /// <param name="SessionID">Session ID</param>
        /// <param name="VariableName">Variable Name</param>
        /// <returns>The value of VariableName</returns>
        public static string GetVariableValue(int SessionID, string VariableName)
        {
            //Retrieves parent Program from DB according to Session ID
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT ProgramID FROM Sessions WHERE ID=@SessionID";
            command.Parameters.Add(new SqlParameter("@SessionID", SessionID));
            int ProgramID = int.Parse(DBHelper.ExecuteScalar(command));

            //Gets variable value according to SessionID and variableID
            return GetVariableValue(SessionID, BEELab.Logic.ProgramManagement.VariableManagement.VariableManagement.GetIDByName(ProgramID, VariableName));
        }

        /// <summary>
        /// Gets SessionID and variableID,
        /// and returns the value of the variable in the Session
        /// </summary>
        /// <param name="SessionID">Session ID</param>
        /// <param name="VariableID">Variable ID</param>
        /// <returns>Variable value in the specific Session (if changed),or its default value</returns>
        public static string GetVariableValue(int SessionID, int VariableID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT Value FROM InitialValues WHERE ParentID=@ParentID AND VariableID=@VariableID AND Type='Session'";
            command.Parameters.Add(new SqlParameter("@ParentID", SessionID));
            command.Parameters.Add(new SqlParameter("@VariableID", VariableID));
            
            string res = DBHelper.ExecuteScalar(command);

            if (res != null)
                return res;

            return VariableManagement.VariableManagement.GetDefaultValue(VariableID);
        }

        /// <summary>
        /// Gets SessionID and returns Session name
        /// </summary>
        /// <param name="SessionID">Session ID</param>
        /// <returns>Session name</returns>
        public static string GetName(int SessionID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT Name FROM Sessions WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", SessionID));
            return DBHelper.ExecuteScalar(command);
        }

        /// <summary>
        /// Gets SessionID and returns Session description
        /// </summary>
        /// <param name="SessionID">Session ID</param>
        /// <returns>Session description</returns>
        public static string GetDescription(int SessionID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT Description FROM Sessions WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", SessionID));
            return DBHelper.ExecuteScalar(command);
        }

        /// <summary>
        /// Gets ProgramID and retuns a list of all sessionIDs that related to this Program
        /// </summary>
        /// <param name="ID">Program ID</param>
        /// <returns>List of all related sessionIDs</returns>
        public static List<int> ListByExperimentID(int ID)
        {
            List<int> result = new List<int>();

            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT ID FROM Sessions WHERE ProgramID=@ProgramID";
            command.Parameters.Add(new SqlParameter("@ProgramID", ID));
            DataTable dt = DBHelper.ExecuteQuery(command);

            foreach (DataRow dr in dt.Rows)
            {
                result.Add((int)dr["ID"]);
            }

            return result;
        }

        /// <summary>
        /// Gets SubjectID and retrns related Invitation ID
        /// </summary>
        /// <param name="SubjectID">Subject ID</param>
        /// <returns>Related Invitation ID</returns>
        public static int GetInvitationIDBySubjectID(string SubjectID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT InvitationID FROM Subjects WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", SubjectID));
            return int.Parse(DBHelper.ExecuteScalar(command));
        }

        /// <summary>
        /// Gets SessionID and returns parent programID
        /// </summary>
        /// <param name="SessionID">Session ID</param>
        /// <returns>Parent Program ID</returns>
        public static int GetProgramIDBySessionID(int SessionID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT ProgramID FROM Sessions WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", SessionID));
            return int.Parse(DBHelper.ExecuteScalar(command));
        }

        /// <summary>
        /// Gets SubjectID and returns related SessionID
        /// </summary>
        /// <param name="SubjectID">Subject ID</param>
        /// <returns>Related Session ID</returns>
        public static int GetSessionIDBySubjectID(string SubjectID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT SessionID FROM Subjects WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", SubjectID));
            return int.Parse(DBHelper.ExecuteScalar(command));
        }

        /// <summary>
        /// Gets SessionID and returns a list of all relates subject IDs
        /// </summary>
        /// <param name="SessionID">Session ID</param>
        /// <returns>List of all relates subject IDs</returns>
        public static List<string> GetSubjectList(int SessionID)
        {
            List<string> result = new List<string>();

            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT ID FROM Subjects WHERE SessionID=@SessionID";
            command.Parameters.Add(new SqlParameter("@SessionID", SessionID));
            DataTable dt = DBHelper.ExecuteQuery(command);

            foreach (DataRow dr in dt.Rows)
            {
                result.Add((string)dr["ID"]);
            }

            return result;
        }

        /// <summary>
        /// Gets Session ID and retuen session's first stage name from DB.
        /// If First stage didn't changed for this session, will return parent program's first stage.
        /// </summary>
        /// <param name="SessionID">Session ID</param>
        /// <returns>First stage name</returns>
        public static string GetFirstStage(int SessionID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT FirstStage FROM Sessions WHERE ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", SessionID));
            string res = DBHelper.ExecuteScalar(command);

            if (res != null && res.Length > 0)
                return res;

            return ProgramManagement.StageManagement.StageManagement.GetFirstStage(GetProgramIDBySessionID(SessionID).ToString());
        }

        /// <summary>
        /// Gets Session ID and returns SubjectID of the session's parent programs owner
        /// </summary>
        /// <param name="ID">Session ID</param>
        /// <returns>Subject ID of the owner</returns>
        public static int GetOwnerID(int ID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT Programs.OwnerID FROM Sessions JOIN Programs ON Programs.ID=Sessions.ProgramID WHERE Sessions.ID=@ID";
            command.Parameters.Add(new SqlParameter("@ID", ID));
            string res = DBHelper.ExecuteScalar(command);

            if (res != null && res.Length > 0)
            {
                return int.Parse(res);
            }

            return 0;
        }
    }
}